import com.google.gdata.client.projecthosting.*;
import com.google.gdata.data.*;
import com.google.gdata.data.projecthosting.*;
import com.google.gdata.util.ResourceNotFoundException;
import com.google.gdata.util.ServiceForbiddenException;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;

 

/*@Sangeeta
 *Program to download Chrome browser issue database
 * 
 * */

public class issue_downloader_chrome {

	private String project = "chromium";
	
	private Connection conn = null;
    
	private String url = "jdbc:mysql://localhost:3306/";
    private String userName = "root"; 
    private String password = "123";
    
    /*private String url = "jdbc:mysql://localhost:3307/";
    private String userName = "sangeetal"; 
    private String password = "sangeetal";*/
    
    private String dbName = "apsec2014"; 
    private String driver = "com.mysql.jdbc.Driver";
   
    private String TABLE = "issue_chrome_browser";
    private ProjectHostingService myService = null;
	
	private void initdb(){
		 try {
		      Class.forName(driver).newInstance();
		      conn = DriverManager.getConnection(url+dbName,userName,password);
		  
		 } catch (Exception e) {
		      e.printStackTrace();
		 }
	}
	
	private void closedb(){ 
		 try {
		      conn.close();
		    } catch (Exception e) {
		      e.printStackTrace();
		    }
	} 
	
	private void insert(int issueID, String state, String status, Timestamp reported, 
			Timestamp closed, String owner, String title, String description, 
			String area, String type, String pri, String feature, String secSeverity, 
			int security, double timediffhrs, int regression, int performance, 
			int cleanup, int polish, int usability, int crash, int stars, 
			int numcomments, String label_str, String milestone, int movedfrom_count, String os, String cr)
	      {
		
		String insertString ="";
		if(closed!=null)
		{
		 insertString = "insert into " + TABLE + " VALUES (" + issueID + ",'" + state + "','" + status + "','" + 
		reported + "','" + closed + "','" + owner + "','" + title + "','" + description + "','" + area + "','" +
		type + "','" + pri + "','" + feature + "','" + secSeverity + "'," + security + "," + timediffhrs + "," + 
		regression + "," + performance + "," + cleanup + "," + polish + "," +  usability + "," + crash + "," +
		stars + "," + numcomments +",'"+label_str+ "','"+milestone+"',"+movedfrom_count+",'"+os+"','"+cr+"'"+")";
		}
		
		else
		{
			String zero_closed_date = "0000.00.00 00.00.00";
			 insertString = "insert into " + TABLE + " VALUES (" + issueID + ",'" + state + "','" + status + "','" + 
				reported + "','" + zero_closed_date + "','" + owner + "','" + title + "','" + description + "','" + area + "','" +
				type + "','" + pri + "','" + feature + "','" + secSeverity + "'," + security + "," + timediffhrs + "," + 
				regression + "," + performance + "," + cleanup + "," + polish + "," +  usability + "," + crash + "," +
				stars + "," + numcomments +",'"+label_str+ "','"+milestone+"',"+movedfrom_count+",'"+os+"','"+cr+"'"+")";
				
			
		}
		System.out.println(insertString); 
		try {
			
			if(conn!=null)
			{
			  Statement stmt = conn.createStatement();
	   		  stmt.executeUpdate(insertString);
			 stmt.close();
			}
			else
			{
				System.out.println("Chec user name");
				System.exit(0);
			}
			
		} catch(Exception e) {
			System.out.println(e);
			e.printStackTrace();
		}
	}
	
	public String getArea(List<Label> labels){
		String result = "";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.contains("Area")){
				result = label.substring(label.indexOf("Area-")+5, label.length());
				//System.out.println("Area : " + label + " : " + result); 
			} 
		}  
		return result;
	}
	
	public String getPri(List<Label> labels){
		String result = "";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.contains("Pri-")){
				result = label.substring(label.indexOf("Pri-")+4, label.length());
				//System.out.println("Area : " + label + " : " + result); 
			} 
		}  
		return result;
	}
	
	public String getType(List<Label> labels){
		String result = "";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.contains("Type-")){
				result = label.substring(label.indexOf("Type-")+5, label.length());
				//System.out.println("Area : " + label + " : " + result); 
			} 
		}  
		return result;
	}
	
	public String getFeature(List<Label> labels){
		String result = "";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.contains("Feature-")){
				result = label.substring(label.indexOf("Feature-")+8, label.length());
				//System.out.println("Area : " + label + " : " + result); 
			} 
		}  
		return result;
	}
	
	public String getSecSeverity(List<Label> labels){
		String result = "";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.contains("SecSeverity")){
				result = label.substring(label.indexOf("SecSeverity-")+12, label.length());
				//System.out.println("Area : " + label + " : " + result); 
			} 
		}  
		return result;
	}
	
	public String getSecurity(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Security") || label.equalsIgnoreCase("Type-Security")||
					label.equalsIgnoreCase("Type-Bug-Security"))
			{
				result = "1";
			} 
		}  
		return result;
	}
	
	public String getRegression(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Regression") || label.equalsIgnoreCase("Type-Regression")
					||label.equalsIgnoreCase("Type-Bug-Regression"))
			{
				result = "1";
			} 
		}  
		return result;
	}
	
	public String getCleanup(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Cleanup") || label.equalsIgnoreCase("Type-Cleanup")){
				result = "1";
			} 
		}  
		return result;
	}
	
	public String getPerformance(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Performance") || label.equalsIgnoreCase("Type-Performance") || label.equalsIgnoreCase("Stability-Performance")){
				result = "1";
			} 
		}   
		return result;
	}
	
	public String getCrash(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Crash") || label.equalsIgnoreCase("Type-Crash") || label.equalsIgnoreCase("Stability-Crash")){
				result = "1";
			} 
		}   
		return result;
	} 
	
	public String getUsability(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Usability") || label.equalsIgnoreCase("Type-Usability") || label.equalsIgnoreCase("Stability-Usability")){
				result = "1";
			} 
		}   
		return result;
	}
	
	public String getPolish(List<Label> labels){
		String result = "0";
		for(int i=0;i<labels.size();i++){
			Label l = labels.get(i);
			String label = l.getValue();
			//System.out.println("Label : " + label);
			if(label.equalsIgnoreCase("Polish") || label.equalsIgnoreCase("Type-Polish") || label.equalsIgnoreCase("Stability-Polish")){
				result = "1";
			} 
		}   
		return result;
	}
	
public String getLables(List<Label> labels)
	{
	  String label_str = "";
	  for(int i=0;i<labels.size();i++)
	  {
		Label l = labels.get(i);
		//l.getValue();
		System.out.println("i="+labels.get(i));
	    label_str=label_str+","+labels.get(i);
	  }
 return label_str;
}

public int getMovedFromCount(List<Label> labels)
{
	int movedfrom = 0;
	for(int i=0;i<labels.size();i++)
	{
		//Label l = (Label) labels.get(i);
		//l.getValue();
		//System.out.println("i="+labels.get(i));
	   // labelstr=labelstr+","+labels.get(i);
		if(labels.get(i).toString().toLowerCase().contains("MovedFrom"))
		{
			//interupt();
			movedfrom++;
		}
		
	}
	return movedfrom;	
}

public String getMilestone(List<Label> labels)
{ 
	String mstone="";
	for(int i=0;i<labels.size();i++)
	{
	
		if(labels.get(i).toString().toLowerCase().contains("mstone-"))
		{
		    try{
			mstone=labels.get(i).toString().substring(20);
			mstone = mstone.split("}")[0];
			System.out.println("ms="+mstone);
		    }catch(ArrayIndexOutOfBoundsException e)
		    {}
			//interupt();
		}
		else if(labels.get(i).toString().toLowerCase().contains("mstone"))
		{
		
			try{
			mstone=labels.get(i).toString().substring(19);
			mstone = mstone.split("}")[0];
			System.out.println("ms="+mstone);
			}catch(ArrayIndexOutOfBoundsException e)
		    {}
			//interupt();
		}
	}

	return mstone;
}

private String getCr(List<Label> labels)
{
  String cr = "";
  for(int i=0;i<labels.size();i++)
  {
		Label l = labels.get(i);
		String label = l.getValue();
		if(label.contains("Cr-"))
		{
			System.out.println("Label cr: " + label);
			try{
			cr = label.split("-")[1];
			} catch(ArrayIndexOutOfBoundsException e)
			{
				System.out.println("Error in Cr");
			}
		} 
  }		
	return cr;
}
	
private String getOS(List<Label> labels)
{
  String os = "";
  for(int i=0;i<labels.size();i++)
  {
		Label l = labels.get(i);
		String label = l.getValue();
		//System.out.println("Label : " + label);
		try{
		if(label.contains("OS-"))
		{
			os = label.split("-")[1];
		}
		
		}catch(Exception e)
		{
			
		}
  }		
	return os;
}

public void printAllIssueComments(String IssueId) {
		try{
			  URL feedUrl = new URL("https://code.google.com/feeds/issues/p/" + project + "/issues/" + IssueId + "/comments/full");
			  IssueCommentsFeed resultFeed = myService.getFeed(feedUrl, IssueCommentsFeed.class);

			  for (int i = 0; i < resultFeed.getEntries().size(); i++) {
			    IssueCommentsEntry entry = resultFeed.getEntries().get(i);
			 
			    TextContent textContent = (TextContent) entry.getContent();
			    if (textContent != null && textContent.getContent() != null) {
			      HtmlTextConstruct htmlConstruct = (HtmlTextConstruct) textContent.getContent();
			      System.out.println("\t" + htmlConstruct.getHtml());
			    }
			  }
			  System.out.println();
		}catch(Exception e){
			System.out.println(e);
		}
	}
  
	
	public int GetIssueComments(String IssueId) {
		int result = 0;
		try{
			  URL feedUrl = new URL("https://code.google.com/feeds/issues/p/" + project + "/issues/" + IssueId + "/comments/full");
			 
			  ///new
			  IssuesQuery myQuery = new IssuesQuery(feedUrl);
			  myQuery.setMaxResults(10000);
			  IssueCommentsFeed resultFeed = myService.getFeed(myQuery, IssueCommentsFeed.class);
			  
			  ///new
			  
			  
			 //@@NOT A COOMEN/ IssueCommentsFeed resultFeed = myService.getFeed(feedUrl, IssueCommentsFeed.class);
			  result = resultFeed.getEntries().size();
			  
			   //Following will give all the issue comments
			  /* 
			  for (int i = 0; i < resultFeed.getEntries().size(); i++)
			   {
				  
				  System.out.println("\n======================= i ="+(i+1)+"\n");
			    IssueCommentsEntry entry = resultFeed.getEntries().get(i);
			    
			    TextContent textContent = (TextContent) entry.getContent();
			    if (textContent != null && textContent.getContent() != null) {
			      HtmlTextConstruct htmlConstruct = (HtmlTextConstruct) textContent.getContent();
			     // System.out.println("\t" + htmlConstruct.getHtml());// Not a comment
			    }//if
			    
			    System.out.println("\n=======================\n");
			  }*/
			  System.out.println();
			  
		}catch(Exception e){
			System.out.println(e);
		}
		return result;
	}
	

	
	public void extractInformation(ProjectHostingService service, String issueId)
	{
		try
		  { 
			URL feedUrl = new URL("https://code.google.com/feeds/issues/p/"+ project+"/issues/full/" + issueId);
			IssuesEntry entry = service.getEntry(feedUrl, IssuesEntry.class);
			
			System.out.println("-------------------------------------------------------------");
			Id id = entry.getIssueId();
			int issueID = id.getValue();
			List<Label> labels =  entry.getLabels();
			for(int i=0;i<labels.size();i++)
			{
				Label l = labels.get(i);
				l.getValue();
				System.out.println("i="+labels.get(i));
			}
			//interupt();
			String state = entry.getState().getValue().name();
			String status ="";
			try{
			     status= entry.getStatus().getValue();
			}catch (Exception e)
			{System.out.println("Status Exception");
			}
			//System.out.println("KIND : " + entry.getKind());
			//System.out.println(entry.getTextContent().getContent().getPlainText());
			//System.out.println("State : " + entry.getState().getValue().name());
			//System.out.println("Status : " + entry.getStatus().getValue());
			//System.out.println("Get Published : " + entry.getPublished());
			//System.out.println("Stars : " + entry.getStars());
			//System.out.println("Closed Date : " + entry.getClosedDate());
			//System.out.println("ISSUE ID : " + issueID);
			//System.out\.println("OWNER : " + entry.getOwner().getUsername().getValue());
			//System.out.println(entry.getVersionId()); 
		    //System.out.println(entry.getTitle().getPlainText());
		    //System.out.println("-------------------------------------------------------------");
		    
		  //  if(state.equalsIgnoreCase("CLOSED")){
		    //	if( (status.equalsIgnoreCase("Fixed")) || (status.equalsIgnoreCase("Verified")) || (status.equalsIgnoreCase("Duplicate"))){
		    		System.out.println("*************************************************************");
		    		System.out.println("ISSUE ID : " + issueID);
		    		System.out.println("STATE : " + state);
		    		System.out.println("STATUS : " + status);
		    		java.sql.Timestamp reported = new Timestamp(entry.getPublished().getValue());
		    		System.out.println("REPORTED : " + reported);
		    		java.sql.Timestamp closed=null;
		    		double diff = 0.0;
		    		try{
		    		closed= new Timestamp(entry.getClosedDate().getValue().getValue());
		    		System.out.println("CLOSED : " + closed);
		    		
		    		diff = closed.getTime() - reported.getTime();
		    		System.out.println("TIME DIFFERENCE (NOR) : " + diff);
		    		
		    		diff = diff / (60 * 60 * 1000);
		    		System.out.println("TIME DIFFERENCE HOURS : " + diff);
		    		} catch(Exception e)
		    		{
		    			diff = 0.0;
		    		}
		    		List<Person> authors = entry.getAuthors();
		    		Object[] authors_o = authors.toArray();
		    		for(int i=0;i<authors_o.length;i++){
		    			Person p = (Person)authors_o[i];
		    			//System.out.println(p.getName() + " : " + p.getUri());
		    			System.out.println("REPORTER NAME : " + p.getName());
		    			System.out.println("REPORTER URI : " + p.getUri());
		    		}
		    		
		    		System.out.println("BLOCKEDONS : " + entry.getBlockedOns().size());
		    		System.out.println("BLOCKINGS : " + entry.getBlockings().size());
		    		System.out.println("NUMCC : " + entry.getCcs());
		    		
		    		
		    		String owner = "";
		    		if(entry.getOwner() != null){
		    			owner = entry.getOwner().getUsername().getValue();
		    			
		    		}
		    		System.out.println("OWNER : " + owner);
		    		
		    		String title = entry.getTitle().getPlainText();
		    		title = title.replaceAll("'", ""); 
		    		System.out.println("TITLE : " + title);  
		    		
		    		String description = entry.getTextContent().getContent().getPlainText();
		    		//System.out.println("DESCRIPTION : " + description);
		    		description = description.replaceAll("'", "");
		    		String area ="";
		    		area= getArea(labels);
		    		System.out.println("AREA : " + area);
		    		
		    		String type = "";
		    		type= getType(labels);
		    		System.out.println("TYPE : " + type);
		    		
		    		String pri="";
		    		pri= getPri(labels);
		    		System.out.println("PRI : " + pri);
		    		
		    		String feature="";
		    		feature= getFeature(labels);
		    		System.out.println("FEATURE : " + feature); 
		    		
		    		String secSeverity="";
		    		secSeverity= getSecSeverity(labels);
		    		System.out.println("SECSEVERITY : " + secSeverity);
		    		
		    		int security=0;
		    		security= Integer.parseInt(getSecurity(labels));
		    		System.out.println("SECURITY : " + security);
		    		 
		    		int regression=0;
		    		regression= Integer.parseInt(getRegression(labels));
		    		System.out.println("REGRESSION : " + regression);
		    		
		    		int performance=0;
		    		performance=Integer.parseInt( getPerformance(labels));
		    		System.out.println("PERFORMANCE : " + performance);
		    		
		    		int cleanup=0;
		    		cleanup= Integer.parseInt(getCleanup(labels));
		    		System.out.println("CLEANUP : " + cleanup);
		    		
		    		int polish=0;
		    		polish= Integer.parseInt(getPolish(labels));
		    		System.out.println("POLISH : " + polish);
		    		
		    		int usability=0;
		    		usability= Integer.parseInt(getUsability(labels));
		    		System.out.println("USABILITY : " + usability);
		    		
		    		int crash=0;
		    		crash= Integer.parseInt(getCrash(labels));
		    		System.out.println("CRASH : " + crash);
		    		
		    		int stars=0;
		    		stars= entry.getStars().getValue().intValue();
		    		System.out.println("STARS : " + stars); 
		    		
		    		int numIssueComments=0;
		    		numIssueComments= GetIssueComments(issueId);
		    		System.out.println("NUM ISSUE COMMENTS : " + numIssueComments); 
		    		
		    		String label_str="";
		    		label_str= getLables(labels);
		    		System.out.println("Lables="+label_str);
		    		
		    		String milestone="";
		    		milestone=  getMilestone(labels);
		    		System.out.println("Milestone:+ milestone");
		    		
		    		int movedfrom_count=0;
		    		movedfrom_count= getMovedFromCount(labels);
		    		System.out.println("Moved from"+ movedfrom_count);
		    		
		    		
		    		
		    		String os="";
		    		os= getOS(labels);
		    		System.out.println("Operating System"+os);
		    		
		    		String cr="";
		    		cr= getCr(labels); 
		    		System.out.println(" cr= "+cr);
		    		System.out.println("*************************************************************");
		    		insert(issueID, state, status, reported, closed, owner, title, description, area, type, pri, feature, secSeverity, security, diff, regression, performance, 
		    				cleanup, polish, usability, crash, stars, numIssueComments, label_str, milestone,
		    				movedfrom_count,os,cr); 
		    	//}
		    //}
		}catch(ServiceForbiddenException e)
		{
			System.out.println("Not allowed");
		}
			
		catch(ResourceNotFoundException e)
		{
			System.out.println("Not allowed");
		}
		catch(Exception e){
			System.out.println(e);
			e.printStackTrace();
			System.exit(0);
		}
	}
	


	public void specificIssue(ProjectHostingService service, String issueId){
		try{
			URL feedUrl = new URL("https://code.google.com/feeds/issues/p/chromium-os/issues/full/" + issueId);
			IssuesEntry entry = service.getEntry(feedUrl, IssuesEntry.class);
			System.out.println("-------------------------------------------------------------");
			Id id = entry.getIssueId();
			int issueID = id.getValue();
			List<Label> labels =  entry.getLabels();
			for(int i=0;i<labels.size();i++)
			{
				Label l = labels.get(i);
				String label = l.getValue();
				System.out.println("Label : " + label); 
			}
			//interupt();
			//System.out.println("KIND : " + entry.getKind());
			//System.out.println(entry.getTextContent().getContent().getPlainText());
			System.out.println("State : " + entry.getState().getValue().name());
			System.out.println("Status : " + entry.getStatus().getValue());
			System.out.println("Get Published : " + entry.getPublished());
			System.out.println("Stars : " + entry.getStars());
			System.out.println("Closed Date : " + entry.getClosedDate());
			System.out.println("ISSUE ID : " + issueID);
			
			System.out.println("OWNER : " + entry.getOwner().getUsername().getValue());
			//System.out.println(entry.getVersionId()); 
		    System.out.println(entry.getTitle().getPlainText());
		    System.out.println("-------------------------------------------------------------");
		}catch(Exception e){
			System.out.println(e);
			e.printStackTrace();
			System.exit(0);
		}
	}
	  
	public void process(){
		try{
			myService = new ProjectHostingService("Sample Application");
			printAllIssues(myService, project);
			
			for(int i=300377;i<=400000;i++){
				extractInformation(myService, Integer.toString(i)); 
				//interupt();
			}
			
			//specificIssue(myService, "60055");
			//extractInformation(myService, "60055"); 
		}catch(Exception e){
			System.out.println(e);
			e.printStackTrace();
			
		}
	}
	
	public void interupt()
	{
		
		BufferedReader br =  new BufferedReader(new InputStreamReader(System.in));
		System.out.println("Intr");
		try {
			br.readLine();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void printAllIssues(ProjectHostingService myService, String project){
		try{
			URL feedUrl = new URL("https://code.google.com/feeds/issues/p/" + project + "/issues/full");
			IssuesFeed resultFeed = myService.getFeed(feedUrl, IssuesFeed.class);
			for (int i = 0; i < resultFeed.getEntries().size(); i++) {
				IssuesEntry entry = resultFeed.getEntries().get(i);
				System.out.println("-------------------------------------------------------------");
				System.out.println("\t" + entry.getId());
			    System.out.println("\t" + entry.getTitle().getPlainText());
			    System.out.println("-------------------------------------------------------------");
			}
			System.out.println();
		}catch(Exception e){
			System.out.println(e);
		}
	} 
	
	public static void main(String[] args){
		issue_downloader_chrome ite = new issue_downloader_chrome();
		ite.initdb();
		ite.process();
		ite.closedb();
	}
}

